//
//  ORMDb.m
//  storeapp
//
//  Created by huangyi on 13-4-25.
//  Copyright (c) 2013年 ios. All rights reserved.
//

#import "SQLiteKit.h"

#import "FMDatabase.h"
#import "FMDatabaseAdditions.h"
#import "FMResultSet.h"

@interface SQLiteKit ()

@property(nonatomic,strong)FMDatabase *database;
@property(nonatomic)BOOL isFrozen;

@end

@implementation SQLiteKit

-(void)dealloc{
    [self close];
}
-(NSString*)description{
    FMDatabase *db=self.database;
    NSString *string=[NSString stringWithFormat:@"SQLiteKit: %@",db.databasePath];
    return string;
}

-(instancetype)init{
    return [self initWithDatabase:@":memory:"];
}
-(instancetype)initWithDatabase:(NSString*)dbfile{
    self=[super init];
    if (self) {
        FMDatabase *db=[FMDatabase databaseWithPath:dbfile];
        self.database=db;
        [self open];
    }
    return self;
}
+(BOOL)isSQLiteThreadSafe{
    return [FMDatabase isSQLiteThreadSafe];
}

-(BOOL)open{
    FMDatabase *db=self.database;
    return [db open];
}
-(BOOL)close{
    FMDatabase *db=self.database;
    return [db close];
}
-(BOOL)reload{
    [self close];
    return [self open];
}

-(NSArray*)getSchema{
    NSMutableArray *array=[NSMutableArray array];
    FMDatabase *db=self.database;
    FMResultSet *set=[db getSchema];
    while ([set next]) {
        NSDictionary *row=set.resultDictionary;
        row=[row dictionaryByRemoveNullValue];
        [array addObject:row];
    }
    return [NSArray arrayWithArray:array];
}
-(NSArray*)listOfTables{
    NSMutableArray *array=[NSMutableArray array];
    NSArray *schema=[self getSchema];
    for (NSDictionary *desc in schema) {
        NSString *type=desc[@"type"];
        if ([type isEqualToString:@"table"]) {
            [array addObject:desc[@"name"]];
        }
        if ([type isEqualToString:@"view"]) {
            [array addObject:desc[@"name"]];
        }
    }
    return [NSArray arrayWithArray:array];
}
-(NSArray*)getTableSchema:(NSString*)table{
    NSMutableArray *array=[NSMutableArray array];
    FMDatabase *db=self.database;
    FMResultSet *set=[db getTableSchema:table];
    while ([set next]) {
        NSDictionary *row=set.resultDictionary;
        row=[row dictionaryByRemoveNullValue];
        [array addObject:row];
    }
    return [NSArray arrayWithArray:array];
}
-(NSArray*)listOfFields:(NSString*)table{
    NSMutableArray *array=[NSMutableArray array];
    NSArray *schema=[self getTableSchema:table];
    for (NSDictionary *desc in schema) {
        [array addObject:desc[@"name"]];
    }
    return [NSArray arrayWithArray:array];
}

-(NSUInteger)count:(NSString*)table{
    NSString *sql=[NSString stringWithFormat:@"SELECT COUNT(rowid) FROM %@",table];
    NSString *num=[self getCell:sql];
    NSInteger row=num.integerValue;
    return row;
}

-(NSInteger)insertOne:(NSDictionary *)object intoTable:(NSString *)table{
    return [self putOne:object intoTable:table];
}
-(NSInteger)putOne:(NSDictionary*)object intoTable:(NSString*)table{
    [self alterTable:table forType:object];
    NSArray *structure=[self listOfFields:table];
    NSInteger tableRowId=NSNotFound;
    if (object[@"rowid"]) {
        NSNumber *rowid=[object valueForKey:@"rowid"];
        tableRowId=[rowid integerValue];
    }
    NSString *row=[self getCell:[NSString stringWithFormat:@"SELECT rowid FROM %@ WHERE rowid=%@",table,@(tableRowId)]];
    if (!row) {
        tableRowId=NSNotFound;
    }
    if (tableRowId==NSNotFound) {
        NSMutableArray *fields=[NSMutableArray array];
        NSMutableArray *values=[NSMutableArray array];
        [object enumerateKeysAndObjectsUsingBlock:^(id key, id obj, BOOL *stop) {
            NSString *field=key;
            if ([structure containsString:field]) {
                NSString *value=obj;
                if (value) {
                    [fields addObject:field];
                    [values addObject:[NSString stringWithFormat:@"'%@'",value]];
                }
            }
        }];
        NSString *k=[fields componentsJoinedByString:@","];
        NSString *v=[values componentsJoinedByString:@","];
        NSString *sql=[NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES(%@)",table,k,v];
        if ([self runSql:sql]) {
            return [self lastInsertID];
        }else{
            return NSNotFound;
        }
    }else{
        [self beginTransaction];
        __block BOOL success=YES;
        [object enumerateKeysAndObjectsUsingBlock:^(id key, id obj, BOOL *stop) {
            NSString *field=key;
            if ([structure containsString:field]) {
                if (![field isEqualToString:@"id"]) {
                    NSString *value=obj;
                    NSString *sql=[NSString stringWithFormat:@"UPDATE %@ SET %@='%@' WHERE rowid=%@",table,field,value,@(tableRowId)];
                    if (![self runSql:sql]) {
                        success=NO;
                    }
                }
            }
        }];
        if (success) {
            [self commit];
            return tableRowId;
        }else{
            [self rollback];
            return NSNotFound;
        }
    }
}

-(BOOL)wipe:(NSString*)table{
    NSString *sql=[NSString stringWithFormat:@"DROP TABLE %@",table];
    return [self runSql:sql];
}
-(BOOL)optimize{
    NSString *sql=@"VACUUM";
    return [self runSql:sql];
}

-(BOOL)alterTable:(NSString*)tableName forType:(NSDictionary*)object{
    [self beginTransaction];
    BOOL isTableExist=YES;
    NSArray *tables=[self listOfTables];
    if (![tables containsString:tableName]) {
        NSString *sql=[NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('rowid' INTEGER PRIMARY KEY AUTOINCREMENT,'mod_time' TEXT)",tableName];
        isTableExist=[self runSql:sql];
    }
    BOOL isTableAlter=YES;
    if (isTableExist) {
        NSArray *fields=[self listOfFields:tableName];
        NSArray *keys=[object allKeys];
        for (NSString *key in keys) {
            if (![fields containsString:key]) {
                NSString *type=@"TEXT";
//                id value=object[key];
//                if ([value isKindOfClass:[NSNumber class]]) {
//                    type=@"REAL";
//                }
                NSString *sql=[NSString stringWithFormat:@"ALTER TABLE %@ ADD COLUMN '%@' %@",tableName,key,type];
                isTableAlter=[self runSql:sql];
            }
        }
    }
    [self commit];
    return isTableAlter;
}

-(BOOL)beginTransaction{
    FMDatabase *db=self.database;
    if (db.inTransaction==NO) {
        [db beginTransaction];
    }
    return db.inTransaction?YES:NO;
}
-(BOOL)commit{
    FMDatabase *db=self.database;
    if (db.inTransaction==YES) {
        [db commit];
    }
    return db.inTransaction?NO:YES;
}
-(BOOL)rollback{
    FMDatabase *db=self.database;
    if (db.inTransaction==YES) {
        [db rollback];
    }
    return db.inTransaction?NO:YES;
}

-(NSDictionary*)getRow:(NSString*)sql{
    if (sql.length==0) {
        return nil;
    }
    NSDictionary *row=nil;
    FMDatabase *db=self.database;
    FMResultSet *set=[db executeQuery:sql];
    if ([set next]) {
        row=set.resultDictionary;
        row=[row dictionaryByRemoveNullValue];
    }
    return row;
}
-(NSArray*)getCol:(NSString*)sql{
    if (sql.length==0) {
        return nil;
    }
    NSMutableArray *array=[NSMutableArray array];
    FMDatabase *db=self.database;
    FMResultSet *set=[db executeQuery:sql];
    while ([set next]) {
        NSString *value=[set stringForColumnIndex:0];
        if (value) {
            [array addObject:[set stringForColumnIndex:0]];
        }
    }
    return [array arrayByRemoveNullValue];
}
-(NSArray*)getAll:(NSString*)sql{
    if (sql.length==0) {
        return nil;
    }
    NSMutableArray *array=[NSMutableArray array];
    FMDatabase *db=self.database;
    FMResultSet *set=[db executeQuery:sql];
    while ([set next]) {
        NSDictionary *row=set.resultDictionary;
        row=[row dictionaryByRemoveNullValue];
        [array addObject:row];
    }
    return array;
}
-(NSString*)getCell:(NSString*)sql{
    if (sql.length==0) {
        return nil;
    }
    NSString *cell=nil;
    FMDatabase *db=self.database;
    FMResultSet *set=[db executeQuery:sql];
    if ([set next]) {
        cell=[set stringForColumnIndex:0];
    }
    return cell;
}
-(BOOL)runSql:(NSString*)sql{
    if (sql.length==0) {
        return NO;
    }
    FMDatabase *db=self.database;
    BOOL result=[db executeUpdate:sql];
    return result;
}
-(NSInteger)lastInsertID{
    FMDatabase *db=self.database;
    sqlite_int64 i=[db lastInsertRowId];
    if (NSIntegerMax>i && NSIntegerMin<i){
        return (NSInteger)i;
    }else{
        return NSNotFound;
    }
}
-(NSDictionary*)lastInsertRow:(NSString*)table{
    NSNumber *row=@(self.lastInsertID);
    NSString *sql=[NSString stringWithFormat:@"SELECT * FROM %@ WHERE rowid='%@'",table,row];
    return [self getRow:sql];
}

-(NSDictionary*)findOne:(NSDictionary*)where fromTable:(NSString*)table{
    NSDictionary *rec=nil;
    NSMutableArray *arr=[NSMutableArray array];
    [arr addObject:@"1"];
    NSArray *fields=[self listOfFields:table];
    [where enumerateKeysAndObjectsUsingBlock:^(id key, id obj, BOOL *stop) {
        if ([fields containsString:key]) {
            NSString *fmt=[NSString stringWithFormat:@"%@='%@'",key,obj];
            [arr addObject:fmt];
        }
    }];
    NSString *condition=[arr componentsJoinedByString:@" and "];
    FMDatabase *db=self.database;
    NSString *sql=[NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@",table,condition];
    FMResultSet *set=[db executeQuery:sql];
    if ([set next]) {
        rec=[set resultDictionary];
    }
    return [rec dictionaryByRemoveNullValue];
}
-(NSArray*)findAll:(NSDictionary*)where fromTable:(NSString*)table orderBy:(NSString*)order{
    NSMutableArray *arr=[NSMutableArray array];
    [arr addObject:@"1"];
    NSArray *fields=[self listOfFields:table];
    [where enumerateKeysAndObjectsUsingBlock:^(id key, id obj, BOOL *stop) {
        if ([fields containsString:key]) {
            NSString *fmt=[NSString stringWithFormat:@"%@='%@'",key,obj];
            [arr addObject:fmt];
        }
    }];
    NSString *condition=[arr componentsJoinedByString:@" and "];
    if (order==nil) {
        order=@"rowid asc";
    }
    NSMutableArray *found=[NSMutableArray array];
    FMDatabase *db=self.database;
    NSString *sql=[NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ order by %@",table,condition,order];
    FMResultSet *set=[db executeQuery:sql];
    while ([set next]) {
        NSDictionary *rec=[set resultDictionary];
        if (rec) {
            [found addObject:[rec dictionaryByRemoveNullValue]];
        }
    }
    return [NSArray arrayWithArray:found];
}

@end
